﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;

namespace 数据转换小工具
{
    public partial class DateConvertTool : Form
    {
        // 配置文件。
        XmlDocument _doc;
        // 待转的数据库集合。
        private CalalogDetails _localcalaloglist = new CalalogDetails();
        // 要转入的数据库集合。
        private CalalogDetails _targetcalaloglist = new CalalogDetails();
        // 待转表所有被选择的表集合。
        private Dictionary<string, string> _firstselectedtablenames = new Dictionary<string, string>();
        // 最后一个选中的待选表。
        private string _lasttablename = string.Empty;


        private string _frist = string.Empty;
        // 上一个待转表的所有字段。
        private List<Date> _firstdatelist = new List<Date>();
        // 当前的待转表所有字段。
        private List<Date> _seconddatelist = new List<Date>();
        // 关联关系集合。
        private List<Relevance> _relevancelist = new List<Relevance>();

        private Graphics _lstLocalG;
        private Graphics _lstTargetG;
        private List<Rectangle> _rectlist = new List<Rectangle>();

        /// <summary>
        /// 构造函数。
        /// </summary>
        public DateConvertTool()
        {
            InitializeComponent();
        }
        /// <summary>
        /// 初始化。
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DateConvertTool_Load(object sender, EventArgs e)
        {
            btnTestLocal.Tag = cmbLocal;
            btnTestTarget.Tag = cmbTarget;
            //_lstLocalG = lstLocalTables.CreateGraphics();
            //_lstTargetG = lstTargetTables.CreateGraphics();

            try
            {
                // 初始化日志
                DubugLog.Init();

                loadConfig();

                #region 获取待转的数据库集合

                cmbLocal.DataSource = _localcalaloglist;
                cmbLocal.DisplayMember = "Name";
                cmbLocal.ValueMember = "Name";
                cmbLocal.SelectedIndex = 0;
                #endregion

                #region 获取新的数据库集合

                cmbTarget.DataSource = _targetcalaloglist;
                cmbTarget.ValueMember = "Name";
                cmbTarget.DisplayMember = "Name";
                cmbTarget.SelectedIndex = 0;
                #endregion
            }
            catch (Exception ex)
            {
                MessageBox.Show("配置文件处理失败：\r\n" + ex.ToString(), "提示", MessageBoxButtons.OK);
                DubugLog.Dubug(DateTime.Now.ToString() + ":配置文件处理失败：\r\n" + ex.ToString());
            }
        }

        private void loadConfig()
        {
            _localcalaloglist.Clear();
            _targetcalaloglist.Clear();
            // 读取配置文件
            _doc = new XmlDocument();
            _doc.Load(Application.StartupPath + "\\配置文件\\config.xml");

            XmlNode localnodest = _doc.SelectSingleNode("//Locals");
            // 循环待转数据库节点的集合。
            foreach (XmlNode node in localnodest.ChildNodes)
            {
                Calalog c = _localcalaloglist.NewCalalog(node.Attributes["name"].Value, node.Attributes["datasource"].Value, node.Attributes["catalog"].Value, node.Attributes["username"].Value, node.Attributes["password"].Value, node.Attributes["version"].Value);
                _localcalaloglist.Add(c);
            }

            XmlNode targetnodest = _doc.SelectSingleNode("//Targets");
            // 循环待转数据库节点的集合。
            foreach (XmlNode node in targetnodest.ChildNodes)
            {
                Calalog c = _localcalaloglist.NewCalalog(node.Attributes["name"].Value, node.Attributes["datasource"].Value, node.Attributes["catalog"].Value, node.Attributes["username"].Value, node.Attributes["password"].Value, node.Attributes["version"].Value);
                _targetcalaloglist.Add(c);
            }
        }
        /// <summary>
        /// 待转数据库选择触发事件。
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cmb_SelectedIndexChanged(object sender, EventArgs e)
        {
            ComboBox cmb = sender as ComboBox;
            if (cmb.SelectedItem != null)
            {
                Calalog c = cmb.SelectedItem as Calalog;
                if (cmb.Name == "cmbLocal")
                {
                    txtLocalCatalog.Text = c.DataSource;
                    txtLocalUser.Text = c.Username;
                    txtLocalPassWord.Text = c.Password;
                    lbLocalVersion.Text = "(" + c.Version + ")";
                    cmbLocalCatalog.Items.Clear();
                }
                else
                {
                    txtTargetCatalog.Text = c.DataSource;
                    txtTargetUser.Text = c.Username;
                    txtTargetPassWord.Text = c.Password;
                    lbTargetVersion.Text = "(" + c.Version + ")";
                    cmbTargetCatalog.Items.Clear();
                }

                //MessageBox.Show(c.Sql+" 连接失败！");
                // 绑定默认值。
                if (cmb.Name == "cmbLocal")
                {
                    cmbLocalCatalog.Items.Add(c.Catalog);
                    cmbLocalCatalog.SelectedIndex = 0;
                }
                else
                {
                    cmbTargetCatalog.Items.Add(c.Catalog);
                    cmbTargetCatalog.SelectedIndex = 0;
                }
            }
        }
        /// <summary>
        /// 测试
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnTest_Click(object sender, EventArgs e)
        {
            Button bt = sender as Button;
            ComboBox cmb = bt.Tag as ComboBox;

            if (cmb.SelectedItem != null)
            {
                Calalog c = cmb.SelectedItem as Calalog;
                if (bt.Name == "btnTestLocal")
                {
                    lstLocalTables.Items.Clear();
                    c.DataSource = txtLocalCatalog.Text;
                    c.Username = txtLocalUser.Text;
                    c.Password = txtLocalPassWord.Text;
                    c.Catalog = cmbLocalCatalog.SelectedItem == null ? "master" : cmbLocalCatalog.SelectedItem.ToString();
                    c.Version = "2008";
                }
                else
                {
                    lstTargetTables.Items.Clear();
                    c.DataSource = txtTargetCatalog.Text;
                    c.Username = txtTargetUser.Text;
                    c.Password = txtTargetPassWord.Text;
                    c.Catalog = cmbTargetCatalog.SelectedItem == null ? "master" : cmbTargetCatalog.SelectedItem.ToString();
                    c.Version = "2008";
                }
                try
                {

                    OleDbConnection con = new OleDbConnection(c.Sql);
                    con.Open();
                    OleDbCommand com = new OleDbCommand("SELECT Name FROM " + c.Catalog + "..SysObjects Where XType='U' ORDER BY Name", con);
                    OleDbDataReader odr = com.ExecuteReader();
                    while (odr.Read())
                    {
                        if (bt.Name == "btnTestLocal")
                            lstLocalTables.Items.Add(odr[0].ToString());
                        else
                            lstTargetTables.Items.Add(odr[0].ToString());
                    }

                    con.Close();
                }
                catch
                {
                    MessageBox.Show(c.Sql + "数据库连接测试失败！" + "提示");
                }
            }
        }
        private void cmb_DropDown(object sender, EventArgs e)
        {
            Calalog c;
            if ((cmbLocalCatalog == (sender as ComboBox)))
            {
                cmbLocalCatalog.Items.Clear();
                c = new Calalog("", txtLocalCatalog.Text, "master", txtLocalUser.Text, txtLocalPassWord.Text, "2008");
            }
            else
            {
                cmbTargetCatalog.Items.Clear();
                c = new Calalog("", txtTargetCatalog.Text, "master", txtTargetUser.Text, txtTargetPassWord.Text, "2008");
            }

            OleDbConnection con = new OleDbConnection(c.Sql);
            con.Open();
            OleDbCommand com1 = new OleDbCommand("SELECT Name FROM Master..SysDatabases ORDER BY Name", con);
            OleDbDataReader odr1 = com1.ExecuteReader();

            while (odr1.Read())
            {
                if ((cmbLocalCatalog == (sender as ComboBox)))
                    cmbLocalCatalog.Items.Add(odr1[0].ToString());
                else
                    cmbTargetCatalog.Items.Add(odr1[0].ToString());
            }
            con.Close();
        }
        private void btnConfig_Click(object sender, EventArgs e)
        {
            if (lstLocalTables.SelectedItems.Count == 1 && lstTargetTables.SelectedItems.Count == 1)
            {
                (cmbLocal.SelectedItem as Calalog).TableName = lstLocalTables.SelectedItems[0].ToString();
                (cmbTarget.SelectedItem as Calalog).TableName = lstTargetTables.SelectedItems[0].ToString();
                ConfigForm configform = new ConfigForm(cmbLocal.SelectedItem as Calalog, cmbTarget.SelectedItem as Calalog);
                configform.Show();
                if (configform.IsAccess)
                {
                    //_rectlist.Add(lstLocalTables.SelectedItems[0]); 
                }
                _relevancelist.Clear();
                //configform.Close();
            }

            if (lstLocalTables.SelectedItems.Count > 1 && lstTargetTables.SelectedItems.Count == 1)
            {
                string localTableName = string.Empty;
                foreach (var v in lstLocalTables.SelectedItems)
                {
                    localTableName += v.ToString() + "、";
                }
                (cmbLocal.SelectedItem as Calalog).TableName = localTableName.TrimEnd('、');
                (cmbTarget.SelectedItem as Calalog).TableName = lstTargetTables.SelectedItems[0].ToString();
                ConfigForm configform = new ConfigForm(cmbLocal.SelectedItem as Calalog, cmbTarget.SelectedItem as Calalog, _relevancelist);
                configform.ShowDialog();
                _relevancelist.Clear();
            }
        }

        private void lst_DrawItem(object sender, DrawItemEventArgs e)
        {
            //e.DrawBackground();
            //Brush myBrush = Brushes.Black;
            //e.Graphics.FillRectangle(Brushes.Red, e.Bounds);
            //myBrush = Brushes.Black;
            //e.Graphics.DrawString(listBox1.Items[e.Index].ToString(), e.Font, myBrush, e.Bounds, StringFormat.GenericDefault);
            //e.DrawFocusRectangle();
        }

        private void DateConvertTool_Paint(object sender, PaintEventArgs e)
        {

        }

        private void Control_Leave(object sender, EventArgs e)
        {
            switch (((Control)sender).Name)
            {
                case "cmbLocal":

                    break;
            }
        }
        /// <summary>
        /// 数据源选择。
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void lstLocalTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (lstLocalTables.SelectedItems.Count > 1)
            {
                panel1.Visible = true;
                _firstdatelist.Clear();

                panel3.Controls.Clear();
                lbTableName1.Text = _lasttablename;
                Calalog oldcalalog = cmbLocal.SelectedItem as Calalog;
                OleDbConnection con = new OleDbConnection(oldcalalog.Sql);
                con.Open();
                // 加载上一个待转表的所有字段
                OleDbCommand comfirst = new OleDbCommand("select sc.[COLUMN_NAME],sc.[IS_NULLABLE],sc.[DATA_TYPE] from INFORMATION_SCHEMA.COLUMNS sc where sc.[TABLE_NAME]='" + _lasttablename + "'", con);
                OleDbDataReader odrfirst = comfirst.ExecuteReader();
                while (odrfirst.Read())
                {
                    Date date = new Date(odrfirst[0].ToString(), odrfirst[1].ToString() == "YES" ? true : false, odrfirst[2].ToString());
                    _firstdatelist.Add(date);
                }
                odrfirst.Close();
                comfirst.Dispose();
                foreach (var tablename in lstLocalTables.SelectedItems)
                {
                    if (!_firstselectedtablenames.ContainsKey(tablename.ToString()))
                    {
                        _firstselectedtablenames.Add(tablename.ToString(), null);
                        _lasttablename = tablename.ToString();
                    }
                }
                lbTableName2.Text = _lasttablename;
                // 加载当前待转表的所有字段
                OleDbCommand comSecond = new OleDbCommand("select sc.[COLUMN_NAME],sc.[IS_NULLABLE],sc.[DATA_TYPE] from INFORMATION_SCHEMA.COLUMNS sc where sc.[TABLE_NAME]='" + _lasttablename + "'", con);
                OleDbDataReader odrSecond = comSecond.ExecuteReader();
                while (odrSecond.Read())
                {
                    Date date = new Date(odrSecond[0].ToString(), odrSecond[1].ToString() == "YES" ? true : false, odrSecond[2].ToString());
                    _seconddatelist.Add(date);
                }
                odrSecond.Close();
                comSecond.Dispose();
                con.Close();

                int h = 0;
                // 加载控件
                for (int i = 0; i < _firstdatelist.Count; i++)
                {
                    Label lb = new Label();
                    lb.Parent = panel3;
                    lb.Name = "lb" + i;
                    lb.Location = new Point(7, h + 5);
                    lb.Text = _firstdatelist[i].ClounmName;

                    ComboBox cmb = new ComboBox();
                    cmb.Parent = panel3;
                    cmb.Location = new Point(112, h);
                    cmb.Size = new System.Drawing.Size(84, 20);
                    cmb.Name = "cmb" + i;
                    cmb.DataSource = _seconddatelist.ToArray();
                    cmb.DisplayMember = "ClounmName";
                    if (i < _seconddatelist.Count)
                    {
                        cmb.SelectedIndex = i;
                    }
                    else
                        cmb.SelectedIndex = _seconddatelist.Count - 1;

                    CheckBox chb = new CheckBox();
                    chb.Parent = panel3;
                    chb.Name = "chb" + i;
                    chb.Location = new Point(223, h + 4);
                    chb.Size = new Size(15, 14);
                    h += 34;
                }
            }
            else
            {
                if (lstLocalTables.SelectedItems.Count == 1)
                {
                    if (_firstselectedtablenames.Count == 0)
                    {
                        _firstselectedtablenames.Add(lstLocalTables.SelectedItems[0].ToString(), null);
                        _frist = string.Empty;
                        _lasttablename = lstLocalTables.SelectedItems[0].ToString();
                        _frist = lstLocalTables.SelectedItems[0].ToString();
                    }
                    else
                    {
                        // 清楚冗余数据
                        _firstselectedtablenames.Clear(); // 清除老表所选的表集合。
                        _frist = string.Empty;
                        _lasttablename = string.Empty; // 清除最后一个所选表名。
                        //_relevancelist.Clear(); // 清除关系。
                        _firstselectedtablenames.Add(lstLocalTables.SelectedItems[0].ToString(), null);
                        _lasttablename = lstLocalTables.SelectedItems[0].ToString();
                        _frist = lstLocalTables.SelectedItems[0].ToString();
                    }
                }
                else
                {
                    // 清楚冗余数据
                    _firstselectedtablenames.Clear(); // 清除老表所选的表集合。
                    _lasttablename = string.Empty; // 清除最后一个所选表名。
                    //_relevancelist.Clear(); // 清除关系。
                }
            }
        }

        private void btnRelevance_Click(object sender, EventArgs e)
        {
            // 关系类。
            Relevance relevance = new Relevance();
            relevance.FirstTable = lbTableName1.Text;
            relevance.SecondTable = lbTableName2.Text;
            for (int i = 0; i < _firstdatelist.Count; i++)
            {
                CheckBox chb = panel3.Controls.Find("chb" + i, false)[0] as CheckBox;
                if (chb.Checked)
                {
                    Label lb = panel3.Controls.Find("lb" + i, false)[0] as Label;
                    ComboBox cmb = panel3.Controls.Find("cmb" + i, false)[0] as ComboBox;
                    relevance.RelevanceList.Add(lb.Text, (cmb.SelectedItem as Date).ClounmName);
                }
            }
            _relevancelist.Add(relevance);
            panel1.Visible = false;
        }

        private void btnDelLocal_Click(object sender, EventArgs e)
        {
            Calalog info = cmbLocal.SelectedItem as Calalog;
            try
            {
                _localcalaloglist.Remove(info);
            }
            catch { }
            //cmbLocal.DataSource = _localcalaloglist;

        }

        private void btnAddServer_Click(object sender, EventArgs e)
        {
            AddCatalog addCatalog = new AddCatalog();
            addCatalog.ShowDialog();
            if (addCatalog.ServerName != "")
            {
                Calalog calalog = new Calalog();
                calalog.Name = addCatalog.ServerName;
                if ((sender as Button) == btnAddLocal)
                {
                    _localcalaloglist.Insert(0, calalog);
                    cmbLocal.SelectedIndex = 0;
                }
                else
                {
                    _targetcalaloglist.Insert(0, calalog);
                    cmbTarget.SelectedIndex = 0;
                }
            }

        }

        private void DateConvertTool_FormClosing(object sender, FormClosingEventArgs e)
        {
            DubugLog.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            _seconddatelist.Clear();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            // 保存local
            string serverName = cmbLocal.Text;
            XmlNode localNode = _doc.SelectSingleNode("//Locals/Local[@name='" + serverName + "']");
            if (localNode == null)
            {
                XmlNode node = CreateNode(0,serverName);
                _doc.FirstChild.ChildNodes[0].AppendChild(node);
            }
            else
            {
                bindNode(0, localNode);
            }
            // 保存target
            serverName = cmbTarget.Text;
            XmlNode targetNode = _doc.SelectSingleNode("//Targets/Target[@name='" + serverName + "']");
            if (targetNode == null)
            {
                XmlNode node = CreateNode(1, serverName);
                _doc.FirstChild.ChildNodes[1].AppendChild(node);
            }
            else
            {
                bindNode(0, targetNode);
            }
            _doc.Save(Application.StartupPath + "\\配置文件\\config.xml");
        }

        private void bindNode(int type, XmlNode node)
        {
            node.Attributes["datasource"].Value = type == 0 ? txtLocalCatalog.Text : txtTargetCatalog.Text;
            node.Attributes["catalog"].Value = type == 0 ? cmbLocalCatalog.Text : cmbTargetCatalog.Text;
            node.Attributes["username"].Value = type == 0 ? txtLocalUser.Text : txtTargetUser.Text;
            node.Attributes["password"].Value = type == 0 ? txtLocalPassWord.Text : txtTargetPassWord.Text;
        }

        private XmlNode CreateNode(int type, string serverName)
        {
            XmlNode node = _doc.CreateNode(XmlNodeType.Element, type == 0 ? "Local" : "Target", null);

            XmlAttribute nameAtr = _doc.CreateAttribute("name");
            nameAtr.Value = serverName;

            XmlAttribute datasourceAtr = _doc.CreateAttribute("datasource");
            datasourceAtr.Value = type == 0 ? txtLocalCatalog.Text : txtTargetCatalog.Text;

            XmlAttribute catalogAtr = _doc.CreateAttribute("catalog");
            catalogAtr.Value = type == 0 ? cmbLocalCatalog.Text : cmbTargetCatalog.Text;

            XmlAttribute usernameAtr = _doc.CreateAttribute("username");
            usernameAtr.Value = type == 0 ? txtLocalUser.Text : txtTargetUser.Text;

            XmlAttribute passwordAtr = _doc.CreateAttribute("password");
            passwordAtr.Value = type == 0 ? txtLocalPassWord.Text : txtTargetPassWord.Text;

            XmlAttribute versionAtr = _doc.CreateAttribute("version");
            versionAtr.Value = "2008";

            node.Attributes.Append(nameAtr);
            node.Attributes.Append(datasourceAtr);
            node.Attributes.Append(catalogAtr);
            node.Attributes.Append(usernameAtr);
            node.Attributes.Append(passwordAtr);
            node.Attributes.Append(versionAtr);

            return node;
        }
    }


}
